﻿using System;
using System.Collections.Generic;
using System.Linq;
using DAL.Common;
using DAL.POCO;
using Dapper;

namespace DAL.Repositories
{
    public class ScoreRepository
    {
        public static IEnumerable<Score> GetScores(string loginName)
        {
            string sql = "SELECT id, userLogin, score, datum FROM [Score] WHERE userLogin = @login ORDER BY id DESC";

            var score = MyConnection.Current.Query<Score>(sql, new { login = loginName });
            return score;
        }

        public static IEnumerable<Score> GetScores(int skip, int limit, out int totalItems, DateTime sinceDate, DateTime tillDate)
        {
            string sql = @"SELECT * FROM
                              (
                                   SELECT ROW_NUMBER() OVER (ORDER BY s.score DESC) as RowNumber, s.id, s.userLogin, s.score, s.datum
                                   FROM [Score] s
                                   WHERE s.datum BETWEEN @since AND @till
                              ) as x
                              WHERE RowNumber BETWEEN @startRow AND @endRow";

            string sqlCount = "SELECT COUNT(id) FROM [Score] WHERE datum BETWEEN @since AND @till";

            var scores = MyConnection.Current.Query<Score>(sql, new { startRow = skip, endRow = skip + limit - 1, since = sinceDate, till = tillDate });

            totalItems = MyConnection.Current.Query<int>(sqlCount, new { since = sinceDate, till = tillDate }).Single();
            return scores;
        }

        public static void Delete(int scoreId)
        {
            string sql = "DELETE FROM [Score] WHERE id = @id";

            MyConnection.Current.Execute(sql, new { id = scoreId });
        }
    }
}
